<?php
/**
 * @package    Blog
 * @subpackage Model
 */

class Project
{
 
    /**
     * @var object Zend DB Adapter
     */
    private $_db;
    /**
     * @var string
     */
   
    public function __construct()
    {
			$this->_db = Zend_Registry::get('db');
    }
    
    public function listProjects()
    {
    	$project_arr = $this->_db->fetchAll("
    		SELECT P.project_id, P.project_title, P.project_type, P.status, C.customer_name, 
					U1.first_name AS BSE_FNAME, U1.last_name AS BSE_LNAME, 
					U2.first_name AS PL_FNAME, U2.last_name AS PL_LNAME, 
					U3.first_name AS DEV_FNAME, U3.last_name AS DEV_LNAME   
    		FROM projects P
    		LEFT JOIN customer C ON P.customer_id = C.customer_id 
    		LEFT JOIN USER U1 ON P.bse_id = U1.userid
    		LEFT JOIN USER U2 ON P.pl_id = U2.userid 		
    		LEFT JOIN USER U3 ON P.dev_id = U3.userid
    	");
    	
    	return $project_arr;
    }
    
    public function getBSEById($user_id)
    {
    	$project_arr = $this->_db->fetchAll("
    		SELECT first_name, last_name
    		FROM user 
    		WHERE userid = " . $user_id . " 
    		AND userlevel = 'bse'
    	");
    	
    	return $project_arr;
    }
    
    public function addProject($form_values)
    {
    	$project_title = $form_values['project_title'];
    	$project_type = $form_values['project_type'];
    	$customer_id = $form_values['customer_id'];
    	$bse_id = $form_values['bse_id'];
    	$pl_id = $form_values['pl_id'];
    	$dev_id = $form_values['dev_id'];
    	$status = $form_values['status'];
    	
    	$add_project = $this->_db->exec("
    		INSERT INTO projects(project_title, project_type, customer_id, bse_id, pl_id, dev_id, status, created_date) 
    		VALUES('" . $project_title . "', '" . $project_type . "', " . $customer_id . ", " . $bse_id . ", " .$pl_id . ", " . $dev_id . ", " . $status . ", now())");
    	
    	if($add_project) {
    		echo "A new project has been successfully created.";
    	}
    }
    
    public function listProjectById($project_id)
    {
    	$project_arr = $this->_db->fetchRow("
    		SELECT project_title, project_type, customer_id, bse_id, pl_id, dev_id, status, created_date
    		FROM projects
    		WHERE project_id = " . $project_id);
    	
    	return $project_arr;
    }
    
    public function updateProject($form_values)
    {
    	$project_title = $form_values['project_title'];
    	$project_type = $form_values['project_type'];
    	$customer_id = $form_values['customer_id'];
    	$bse_id = $form_values['bse_id'];
    	$pl_id = $form_values['pl_id'];
    	$dev_id = $form_values['dev_id'];
    	$status = $form_values['status'];
    	
    	$update_project = $this->_db->exec("
    		UPDATE projects 
    		SET project_title = '" . $project_title . "' , project_type = '" . $project_type . "', customer_id = " . $customer_id . ", bse_id = " . $bse_id . ", pl_id = " . $pl_id . ", dev_id = " . $dev_id . ", status = " . $status . ", created_date = now()
    	");
    	
    	if($update_project)
    	{
    		echo "Project details has been successfully updated.";
    	}
    }
    
    public function delProject($project_id)
    {
    	$del_project = $this->_db->exec("
    		DELETE FROM projects
    		WHERE project_id = " . $project_id);
    	
    	if($del_project)
    	{
    		echo "A project has been successfully deleted.";
    	}
    }
    
    public function assignUsers($project_id, $user_val)
    {
    	print_r($user_val);
    }
}